import { defineEventHandler, getQuery, readBody } from 'h3'
import { query, execute } from '../utils/db'
import { customAlphabet } from 'nanoid'
import mysql from 'mysql2'
import jwt from 'jsonwebtoken'
const { verify } = jwt
import type { DatabaseTypes } from '~/types/home'
import { ResponseCode, sendSuccessResponse, sendErrorResponse } from '~/server/utils/response'
import upsertRows from '~/utils/db-upsert'

const alphabet = process.env.alphabet || '123456789abcdefghijklmnopqrstuvwxyz'
const nanoid = customAlphabet(alphabet, 10)

export default defineEventHandler(async (event) => {
  const method = event.node.req.method

  // 获取首页数据 (GET /api/home)
  if (method === 'GET') {
    try {
      const [
        siteInfo,
        services,
        cases,
        team,
        partners
      ] = await Promise.all([
        query<mysql.RowDataPacket[]>(`
        SELECT 
          company_name as companyName,
          slogan,
         logo_path as logo,
          background_gradient_classes as gradient
        FROM site_info
        LIMIT 1
      `),

        query<mysql.RowDataPacket[]>(`
        SELECT 
          id, title, description, 
          icon_name as icon, 
          icon_color_class as iconColor,
          background_class as iconBg
        FROM services
        WHERE is_active = TRUE
        ORDER BY sort_order ASC
        LIMIT 3
      `),

        query<mysql.RowDataPacket[]>(`
        SELECT 
          id, title, description, 
         image_filename as image,
          image_alt_text as alt
        FROM case_studies
        WHERE is_featured = TRUE
        ORDER BY sort_order ASC
        LIMIT 3
      `),

        query<mysql.RowDataPacket[]>(`
        SELECT 
          id, name, role,
          CONCAT('https://api.dicebear.com/8.x/avataaars/svg?seed=', avatar_seed) as avatar,
          border_color_class as borderColor
        FROM team_members
        ORDER BY sort_order ASC
        LIMIT 3
      `),

        query<mysql.RowDataPacket[]>(`
        SELECT 
          id, name,
          CONCAT('https://api.dicebear.com/8.x/shapes/svg?seed=', logo_seed) as logo
        FROM partners
        ORDER BY sort_order ASC
        LIMIT 6
      `)
      ])
      return sendSuccessResponse(event, {
        banner: {
          companyName: siteInfo.results[0]?.companyName,
          slogan: siteInfo.results[0]?.slogan,
          logo: siteInfo.results[0]?.logo,
          gradient: siteInfo.results[0]?.gradient
        },
        services: services.results,
        cases: cases.results,
        team: team.results,
        partners: partners.results.map(p => p.logo)
      }, '首页列表获取成功')
    } catch (error: any) {
      return sendErrorResponse(event, ResponseCode.BAD_REQUEST, error?.message || '更新首页数据失败')
    }
  }

  // 更新首页数据 (PUT /api/home)
  if (method === 'PUT') {

    try {
      const { banner, services, cases, team, partners } = await readBody<DatabaseTypes>(event)
      // 1. 更新网站信息（只保留一条，直接 update）
      if (banner) {
        await execute(
          `UPDATE site_info SET company_name = ?, slogan = ?, logo_path = ?, background_gradient_classes = ? WHERE id = 1`,
          [
            banner.companyName,
            banner.slogan,
            banner.logo?.startsWith('/uploads/') ? banner.logo.replace('/uploads/', '') : banner.logo,
            banner.gradient
          ]
        )
      }

      // 2. 服务 upsert
      if (Array.isArray(services)) {
        const servicesForDb = services.map(item => ({
          id: item.id,
          title: item.title,
          description: item.description,
          icon_name: item.icon,
          icon_color_class: item.iconColor,
          background_class: item.iconBg
        }))
        await upsertRows('services', servicesForDb)
      }

      // 3. 案例 upsert
      if (Array.isArray(cases)) {
        const casesForDb = cases.map(item => {
          const row: any = {
            title: item.title,
            description: item.description,
            image_filename: item.image?.startsWith('/uploads/cases/')
              ? item.image.replace('/uploads/cases/', '')
              : item.image,
            is_featured: true, // 默认设置为 featured
            image_alt_text: item.alt
          }
          if (item.id) row.id = item.id // 只有有id才加
          return row
        })
        await upsertRows('case_studies', casesForDb)
      }

      // 4. 团队 upsert
      if (Array.isArray(team)) {
        const teamForDb = team
          .filter(item => !!item.name)
          .map(item => ({
            id: item.id,
            name: item.name,
            role: item.role,
            avatar_seed: item.avatar_seed ?? item.name ?? nanoid(),
            sort_order: item.sort_order ?? 0,
            is_leadership: item.is_leadership ?? false
          }))
        await upsertRows('team_members', teamForDb)
      }

      // 5. 合作伙伴 upsert（全量覆盖，先清空再插入）
      if (Array.isArray(partners)) {
        await execute('TRUNCATE TABLE partners')
        if (partners.length > 0) {
          const seen = new Set()
          const values = partners
            .map((logo, idx) => {
              try {
                const url = new URL(logo)
                const seed = url.searchParams.get('seed') || ''
                return [seed, seed, idx]
              } catch {
                return [logo, logo, idx]
              }
            })
            .filter(([name]) => {
              if (seen.has(name)) return false
              seen.add(name)
              return true
            })
          if (values.length > 0) {
            await execute(
              'INSERT INTO partners (name, logo_seed, sort_order) VALUES ?',
              [values]
            )
          }
        }
      }

      return sendSuccessResponse(event, {
        updatedAt: new Date().toISOString(),
        updatedItems: {
          banner,
          services,
          cases,
          team,
          partners
        }
      }, '首页数据更新成功')
      // 1. 更新网站信息
      if (banner) {
        const { logo, ...updateData } = banner
        const updates: string[] = []
        const values: any[] = []

        if (updateData.companyName !== undefined) {
          updates.push('company_name = ?')
          values.push(updateData.companyName)
        }
        if (updateData.slogan !== undefined) {
          updates.push('slogan = ?')
          values.push(updateData.slogan)
        }
        if (updateData.gradient !== undefined) {
          updates.push('background_gradient_classes = ?')
          values.push(updateData.gradient)
        }
        if (logo !== undefined) {
          updates.push('logo_path = ?')
          values.push(logo.startsWith('/uploads/') ? logo.replace('/uploads/', '') : logo)
        }

        if (updates.length > 0) {
          await execute(
            `UPDATE site_info SET ${updates.join(', ')} WHERE id = 1`,
            values
          )
        }
      }

      // 2. 更新服务项（保持您原有的execute调用方式）
      if (services?.length) {
        for (const service of services) {
          const updates: string[] = []
          const values: any[] = []

          if (service.title !== undefined) {
            updates.push('title = ?')
            values.push(service.title)
          }
          if (service.description !== undefined) {
            updates.push('description = ?')
            values.push(service.description)
          }
          if (service.icon !== undefined) {
            updates.push('icon_name = ?')
            values.push(service.icon)
          }
          if (service.iconColor !== undefined) {
            updates.push('icon_color_class = ?')
            values.push(service.iconColor)
          }
          if (service.iconBg !== undefined) {
            updates.push('background_class = ?')
            values.push(service.iconBg)
          }

          if (updates.length > 0 && service.id) {
            await execute(
              `UPDATE services SET ${updates.join(', ')} WHERE id = ?`,
              [...values, service.id]
            )
          }
        }
      }

      // 3. 更新案例（保持原有结构）
      if (cases?.length) {
        for (const caseItem of cases) {
          const updates: string[] = []
          const values: any[] = []

          if (caseItem.title !== undefined) {
            updates.push('title = ?')
            values.push(caseItem.title)
          }
          if (caseItem.description !== undefined) {
            updates.push('description = ?')
            values.push(caseItem.description)
          }
          if (caseItem.alt !== undefined) {
            updates.push('image_alt_text = ?')
            values.push(caseItem.alt)
          }
          if (caseItem.image !== undefined) {
            updates.push('image_filename = ?')
            values.push(caseItem.image.startsWith('/uploads/cases/')
              ? caseItem.image.replace('/uploads/cases/', '')
              : caseItem.image)
          }

          if (updates.length > 0 && caseItem.id) {
            await execute(
              `UPDATE case_studies SET ${updates.join(', ')} WHERE id = ?`,
              [...values, caseItem.id]
            )
          }
        }
      }

      // 4. 更新团队成员（保持原有结构）
      if (team?.length) {
        for (const member of team) {
          const updates: string[] = []
          const values: any[] = []

          if (member.name !== undefined) {
            updates.push('name = ?')
            values.push(member.name)
          }
          if (member.role !== undefined) {
            updates.push('role = ?')
            values.push(member.role)
          }
          if (member.border_color_class !== undefined) {
            updates.push('border_color_class = ?')
            values.push(member.border_color_class)
          }
          if (member.sort_order !== undefined) {
            updates.push('sort_order = ?')
            values.push(member.sort_order)
          }
          if (member.is_leadership !== undefined) {
            updates.push('is_leadership = ?')
            values.push(member.is_leadership ? 1 : 0)
          }

          if (updates.length > 0 && member.id) {
            await execute(
              `UPDATE team_members SET ${updates.join(', ')} WHERE id = ?`,
              [...values, member.id]
            )
          }
        }
      }

      // 5. 更新合作伙伴（保持原有结构）
      if (partners) {
        // 先清空现有数据
        await execute('TRUNCATE TABLE partners')

        if (partners.length > 0) {
          // 准备批量插入数据
          const values = partners.map(logo => {
            try {
              const url = new URL(logo)
              const seed = url.searchParams.get('seed') || ''
              return [seed, seed] // 第一个是name，第二个是logo_seed
            } catch {
              return [logo, logo] // 如果不是URL，用logo同时作为name和seed
            }
          })

          await execute(
            'INSERT INTO partners (name, logo_seed) VALUES ?',
            [values]
          )
        }
      }

      // 返回成功响应（添加更详细的信息）

      return sendSuccessResponse(event, {
        updatedAt: new Date().toISOString(),
        updatedItems: {
          banner: !!banner,
          services: services?.length || 0,
          cases: cases?.length || 0,
          team: team?.length || 0,
          partners: partners?.length || 0
        }
      }, '首页数据更新成功')

    } catch (error: any) {
      console.error('更新失败:', error)
      return sendErrorResponse(event, ResponseCode.BAD_REQUEST, error || '更新首页数据失败')
      // throw createError({
      //   statusCode: 500,
      //   message: '更新首页数据失败',
      //   data: {
      //     error: error.message,
      //     // 开发环境显示更多信息
      //     ...(process.env.NODE_ENV === 'development' && {
      //       stack: error.stack
      //     })
      //   }
      // })
    }
  }

  // 未匹配的路由
  // throw createError({
  //   statusCode: 404,
  //   message: 'Endpoint not found'
  // })
  return sendErrorResponse(event, ResponseCode.NOT_FOUND, 'Endpoint not found')

})
